﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GFMS.IDAL;

namespace KPIS.GERP.GFMS.DAL
{
    public class D_HRM_PS_Employees : IDAL_HRM_PS_Employees
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();

        public D_HRM_PS_Employees()
        {

        }

        public IList<M_HRM_PS_Employees> GetFull(Nullable<int> emp_type_seq
            , Nullable<int> job_site_seq, Nullable<int> working_job_site_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_HRM_PS_Employees> infoList = new List<M_HRM_PS_Employees>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@emp_type_seq", emp_type_seq);
                dbManager.AddParameters(2, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(3, "@working_job_site_seq", working_job_site_seq);

                strSQL = "select e.emp_seq, e.citizen as old_class_name, np.prefix_name, e.first_name, e.last_name"
                    + ", concat(coalesce(np.prefix_short, np.prefix_name, ''), coalesce(e.first_name, e.first_name_eng, ''), ' ', coalesce(e.last_name, e.last_name_eng, '')) as emp_name"
                    + ", e.emp_type_seq, et.emp_type_name, e.class_seq, c.class_name, e.salary"
                    + ", e.pos_seq, p.pos_ref, p.pos_name, p.job_site_seq, pj.job_site_ref, pj.job_site_name"
                    + ", e.working_job_site_seq, wj.job_site_ref as working_job_site_ref, wj.job_site_name as working_job_site_name"
                    + ", e.emp_status_seq, es.emp_status_name, es.active, e.record_status"
                    + ", wj.work_area_seq"
                    + ", exe.exec_pos_seq, exe.exec_pos_name"
                    + " from hrm_ps_employees e"
                    + " left join mas_name_prefixes np on e.name_prefix_seq = np.name_prefix_seq"
                    + " left join hrm_ps_employee_types et on e.emp_type_seq = et.emp_type_seq"
                    + " left join hrm_mp_classes c on e.class_seq = c.class_seq"
                    //+ " left join hrm_mp_classes oc on e.old_class_seq = oc.class_seq"
                    + " left join hrm_mp_positions p on e.pos_seq = p.pos_seq"
                    + " left join hrm_mp_job_sites pj on p.job_site_seq = pj.job_site_seq"
                    + " left join hrm_mp_job_sites wj on e.working_job_site_seq = wj.job_site_seq"
                    + " left join hrm_mp_exec_positions exe on e.exec_pos_seq = exe.exec_pos_seq"
                    + " left join hrm_ps_employee_statuses es on e.emp_status_seq = es.emp_status_seq"
                    + " where e.record_status not like @delete_status"
                    + (emp_type_seq != null ? " and e.emp_type_seq = @emp_type_seq" : "")
                    + (job_site_seq != null ? " and p.job_site_seq = @job_site_seq" : "")
                    + (working_job_site_seq != null ? " and e.working_job_site_seq = @working_job_site_seq" : "")
                    + " ORDER BY e.first_name";
                

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_HRM_PS_Employees info = new M_HRM_PS_Employees();
                        info.emp_seq = Convert.ToInt32(drc[i]["emp_seq"]);
                        info.name_prefix_name = drc[i]["prefix_name"].ToString();
                        info.first_name = drc[i]["first_name"].ToString();
                        info.last_name = drc[i]["last_name"].ToString();
                        info.emp_name = drc[i]["emp_name"].ToString();
                        info.emp_type_seq = cn.ConvertNullInt(drc[i]["emp_type_seq"]);
                        info.emp_type_name = drc[i]["emp_type_name"].ToString();
                        info.class_seq = cn.ConvertNullInt(drc[i]["class_seq"]);
                        info.class_name = drc[i]["class_name"].ToString();                        
                        //info.old_class_seq = cn.ConvertNullInt(drc[i]["old_class_seq"]);
                        info.old_class_name = drc[i]["old_class_name"].ToString();
                        info.salary = cn.ConvertNullDouble(drc[i]["salary"]);
                        info.pos_seq = cn.ConvertNullInt(drc[i]["pos_seq"]);
                        info.pos_ref = drc[i]["pos_ref"].ToString();
                        info.pos_name = drc[i]["pos_name"].ToString();
                        info.job_site_ref = drc[i]["job_site_ref"].ToString();
                        info.job_site_name = drc[i]["job_site_name"].ToString();
                        info.working_job_site_seq = cn.ConvertNullInt(drc[i]["working_job_site_seq"]);
                        info.working_job_site_ref = drc[i]["working_job_site_ref"].ToString();
                        info.working_job_site_name = drc[i]["working_job_site_name"].ToString();
                        info.emp_status_seq = cn.ConvertNullInt(drc[i]["emp_status_seq"]);
                        info.emp_status_name = drc[i]["working_job_site_name"].ToString();
                        info.work_area_seq = cn.ConvertNullInt(drc[i]["work_area_seq"]);
                        info.exec_pos_seq = cn.ConvertNullInt(drc[i]["exec_pos_seq"]);
                        info.exec_pos_name = drc[i]["exec_pos_name"].ToString();
                        info.active = drc[i]["active"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_HRM_PS_Employees> GetList(Nullable<int> emp_type_seq
            , Nullable<int> job_site_seq, Nullable<int> working_job_site_seq, string filter)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_HRM_PS_Employees> infoList = new List<M_HRM_PS_Employees>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@emp_type_seq", emp_type_seq);
                dbManager.AddParameters(2, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(3, "@working_job_site_seq", working_job_site_seq);
                dbManager.AddParameters(4, "@filter", "%" + filter + "%");

                strSQL = "SELECT *"
                    + " FROM (SELECT e.emp_seq, concat(coalesce(np.prefix_short, np.prefix_name, ''), coalesce(e.first_name, e.first_name_eng, ''), ' ', coalesce(e.last_name, e.last_name_eng, '')) as emp_name"
                        + " from hrm_ps_employees e"
                        + " left join mas_name_prefixes np on e.name_prefix_seq = np.name_prefix_seq"
                        + " left join hrm_mp_positions p on e.pos_seq = p.pos_seq"
                        + " WHERE e.record_status not like @delete_status"
                        + (emp_type_seq != null ? " and e.emp_type_seq = @emp_type_seq" : "")
                        + (job_site_seq != null ? " and p.job_site_seq = @job_site_seq" : "")
                        + (working_job_site_seq != null ? " and e.working_job_site_seq = @working_job_site_seq" : "")
                        + ") t"
                    +(filter.Length > 0 ? " WHERE emp_name like @filter" : "")
                    + " ORDER BY emp_name";
                

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_HRM_PS_Employees info = new M_HRM_PS_Employees();
                        info.emp_seq = Convert.ToInt32(drc[i]["emp_seq"]);
                        info.emp_name = drc[i]["emp_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_HRM_PS_Employees> GetListByEmpType(string emp_type_seq
            , Nullable<int> job_site_seq, Nullable<int> working_job_site_seq, string filter)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_HRM_PS_Employees> infoList = new List<M_HRM_PS_Employees>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@emp_type_seq", emp_type_seq);
                dbManager.AddParameters(2, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(3, "@working_job_site_seq", working_job_site_seq);
                dbManager.AddParameters(4, "@filter", "%" + filter + "%");

                strSQL = "SELECT *"
                    + " FROM (SELECT e.emp_seq"
                        + ", concat(coalesce(np.prefix_short, np.prefix_name, ''), coalesce(e.first_name, e.first_name_eng, ''), ' ', coalesce(e.last_name, e.last_name_eng, '')) as emp_name"
                        + ", exec_pos_name"
                        + " from hrm_ps_employees e"
                        + " left join mas_name_prefixes np on e.name_prefix_seq = np.name_prefix_seq"
                        + " left join hrm_mp_positions p on e.pos_seq = p.pos_seq"
                        + " WHERE e.record_status not like @delete_status"
                        + (!string.IsNullOrEmpty(emp_type_seq) ? " and e.emp_type_seq in " + emp_type_seq : "")
                        + (job_site_seq != null ? " and p.job_site_seq = @job_site_seq" : "")
                        + (working_job_site_seq != null ? " and e.working_job_site_seq = @working_job_site_seq" : "")
                        + ") t"
                    + (!string.IsNullOrEmpty(filter) ? " WHERE emp_name like @filter" : "")
                    + " ORDER BY emp_name";


                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_HRM_PS_Employees info = new M_HRM_PS_Employees();
                        info.emp_seq = Convert.ToInt32(drc[i]["emp_seq"]);
                        info.emp_name = drc[i]["emp_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public DataTable ListEmployeeByType(string strEmpTypeSeq, string strJobSiteSeq)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql;
                sql = " select e.emp_seq as sequence, e.pos_seq as t_pos_seq, e.class_seq as t_class_seq"
                    + ", mp.pos_ref as ref_no, mp.pos_name as posName, mc.class_name as className, js.job_site_name as jobsiteName"
                    + ", ex.exec_pos_name"
                    + " ,CONCAT( np.prefix_name, e.first_name,' ',e.last_name ) full_name"
                    + " from hrm_ps_employees e"
                    + " left join hrm_mp_positions mp on e.pos_seq = mp.pos_seq"
                    + " left join hrm_mp_exec_positions ex on mp.exec_pos_seq = ex.exec_pos_seq"
                    + " left join hrm_mp_job_sites js on e.working_job_site_seq = js.job_site_seq"
                    + " left join hrm_mp_classes mc on e.class_seq = mc.class_seq"
                    + " left join mas_name_prefixes np on e.name_prefix_seq = np.name_prefix_seq"
                    + " left join hrm_ps_employee_statuses es on e.emp_status_seq = es.emp_status_seq"

                    + " where es.active = '1'";
                if (!string.IsNullOrEmpty(strEmpTypeSeq))
                {
                    sql += " and e.emp_type_seq in " + strEmpTypeSeq;
                }
                if (!string.IsNullOrEmpty(strJobSiteSeq))
                {
                    sql += " and e.working_job_site_seq = " + strJobSiteSeq;
                }
                sql += " order by e.first_name, e.last_name";

                DataSet ds;
                DataTable dt;
                ds = new DataSet();
                dbManager.Open();
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {

            }
            return null;
        }

        public M_HRM_PS_Employees GetInfo(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_HRM_PS_Employees info = new M_HRM_PS_Employees();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "select e.emp_seq, np.prefix_name, e.first_name, e.last_name"
                    + ", concat(coalesce(np.prefix_short, np.prefix_name, ''), coalesce(e.first_name, e.first_name_eng, ''), ' ', coalesce(e.last_name, e.last_name_eng, '')) as emp_name"
                    + ", e.emp_type_seq, et.emp_type_name, e.class_seq, c.class_name, e.salary"
                    + ", e.pos_seq, p.pos_ref, p.pos_name, p.job_site_seq, pj.job_site_ref, pj.job_site_name"
                    + ", e.working_job_site_seq, wj.job_site_ref as working_job_site_ref, wj.job_site_name as working_job_site_name"
                    + ", e.emp_status_seq, es.emp_status_name, es.active, e.record_status"
                    + ", wj.work_area_seq"
                    + ", exe.exec_pos_seq, exe.exec_pos_name"
                    + " from hrm_ps_employees e"
                    + " left join mas_name_prefixes np on e.name_prefix_seq = np.name_prefix_seq"
                    + " left join hrm_ps_employee_types et on e.emp_type_seq = et.emp_type_seq"
                    + " left join hrm_mp_classes c on e.class_seq = c.class_seq"
                   // + " left join hrm_mp_classes oc on e.old_class_seq = oc.class_seq"
                    + " left join hrm_mp_positions p on e.pos_seq = p.pos_seq"
                    + " left join hrm_mp_job_sites pj on p.job_site_seq = pj.job_site_seq"
                    + " left join hrm_mp_job_sites wj on e.working_job_site_seq = wj.job_site_seq"
                    + " left join hrm_mp_exec_positions exe on e.exec_pos_seq = exe.exec_pos_seq"
                    + " left join hrm_ps_employee_statuses es on e.emp_status_seq = es.emp_status_seq"
                    + " where e.emp_seq = @seq";
                

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.emp_seq = Convert.ToInt32(drc[0]["emp_seq"]);
                    info.name_prefix_name = drc[0]["prefix_name"].ToString();
                    info.first_name = drc[0]["first_name"].ToString();
                    info.last_name = drc[0]["last_name"].ToString();
                    info.emp_name = drc[0]["emp_name"].ToString();
                    info.emp_type_seq = cn.ConvertNullInt(drc[0]["emp_type_seq"]);
                    info.emp_type_name = drc[0]["emp_type_name"].ToString();
                    info.class_seq = cn.ConvertNullInt(drc[0]["class_seq"]);
                    info.class_name = drc[0]["class_name"].ToString();
                    //info.old_class_seq = cn.ConvertNullInt(drc[0]["old_class_seq"]);
                    //info.old_class_name = drc[0]["old_class_name"].ToString();
                    info.salary = cn.ConvertNullDouble(drc[0]["salary"]);
                    info.pos_seq = cn.ConvertNullInt(drc[0]["pos_seq"]);
                    info.pos_ref = drc[0]["pos_ref"].ToString();
                    info.pos_name = drc[0]["pos_name"].ToString();
                    info.job_site_ref = drc[0]["job_site_ref"].ToString();
                    info.job_site_name = drc[0]["job_site_name"].ToString();
                    info.working_job_site_seq = cn.ConvertNullInt(drc[0]["working_job_site_seq"]);
                    info.working_job_site_ref = drc[0]["working_job_site_ref"].ToString();
                    info.working_job_site_name = drc[0]["working_job_site_name"].ToString();
                    info.emp_status_seq = cn.ConvertNullInt(drc[0]["emp_status_seq"]);
                    info.emp_status_name = drc[0]["working_job_site_name"].ToString();
                    info.work_area_seq = cn.ConvertNullInt(drc[0]["work_area_seq"]);
                    info.exec_pos_seq = cn.ConvertNullInt(drc[0]["exec_pos_seq"]);
                    info.exec_pos_name = drc[0]["exec_pos_name"].ToString();
                    info.active = drc[0]["active"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public M_HRM_PS_Employees GetInfo(string citizen)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_HRM_PS_Employees info = new M_HRM_PS_Employees();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@citizen", citizen.Replace("-", "").Replace(" ", ""));
                dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "select e.emp_seq, np.prefix_name, e.first_name, e.last_name"
                    + ", concat(coalesce(np.prefix_short, np.prefix_name, ''), coalesce(e.first_name, e.first_name_eng, ''), ' ', coalesce(e.last_name, e.last_name_eng, '')) as emp_name"
                    + ", e.emp_type_seq, et.emp_type_name, e.class_seq, c.class_name, e.salary"
                    + ", e.pos_seq, p.pos_ref, p.pos_name, p.job_site_seq, pj.job_site_ref, pj.job_site_name"
                    + ", e.working_job_site_seq, wj.job_site_ref as working_job_site_ref, wj.job_site_name as working_job_site_name"
                    + ", e.emp_status_seq, es.emp_status_name, es.active, e.record_status"
                    + ", wj.work_area_seq"
                    + ", exe.exec_pos_seq, exe.exec_pos_name"
                    + " from hrm_ps_employees e"
                    + " left join mas_name_prefixes np on e.name_prefix_seq = np.name_prefix_seq"
                    + " left join hrm_ps_employee_types et on e.emp_type_seq = et.emp_type_seq"
                    + " left join hrm_mp_classes c on e.class_seq = c.class_seq"
                    // + " left join hrm_mp_classes oc on e.old_class_seq = oc.class_seq"
                    + " left join hrm_mp_positions p on e.pos_seq = p.pos_seq"
                    + " left join hrm_mp_job_sites pj on p.job_site_seq = pj.job_site_seq"
                    + " left join hrm_mp_job_sites wj on e.working_job_site_seq = wj.job_site_seq"
                    + " left join hrm_mp_exec_positions exe on e.exec_pos_seq = exe.exec_pos_seq"
                    + " left join hrm_ps_employee_statuses es on e.emp_status_seq = es.emp_status_seq"
                    + " where e.citizen = @citizen"
                    + " and e.record_status not like 'D'";


                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.emp_seq = Convert.ToInt32(drc[0]["emp_seq"]);
                    info.name_prefix_name = drc[0]["prefix_name"].ToString();
                    info.first_name = drc[0]["first_name"].ToString();
                    info.last_name = drc[0]["last_name"].ToString();
                    info.emp_name = drc[0]["emp_name"].ToString();
                    info.emp_type_seq = cn.ConvertNullInt(drc[0]["emp_type_seq"]);
                    info.emp_type_name = drc[0]["emp_type_name"].ToString();
                    info.class_seq = cn.ConvertNullInt(drc[0]["class_seq"]);
                    info.class_name = drc[0]["class_name"].ToString();
                    //info.old_class_seq = cn.ConvertNullInt(drc[0]["old_class_seq"]);
                    //info.old_class_name = drc[0]["old_class_name"].ToString();
                    info.salary = cn.ConvertNullDouble(drc[0]["salary"]);
                    info.pos_seq = cn.ConvertNullInt(drc[0]["pos_seq"]);
                    info.pos_ref = drc[0]["pos_ref"].ToString();
                    info.pos_name = drc[0]["pos_name"].ToString();
                    info.job_site_ref = drc[0]["job_site_ref"].ToString();
                    info.job_site_name = drc[0]["job_site_name"].ToString();
                    info.working_job_site_seq = cn.ConvertNullInt(drc[0]["working_job_site_seq"]);
                    info.working_job_site_ref = drc[0]["working_job_site_ref"].ToString();
                    info.working_job_site_name = drc[0]["working_job_site_name"].ToString();
                    info.emp_status_seq = cn.ConvertNullInt(drc[0]["emp_status_seq"]);
                    info.emp_status_name = drc[0]["working_job_site_name"].ToString();
                    info.work_area_seq = cn.ConvertNullInt(drc[0]["work_area_seq"]);
                    info.exec_pos_seq = cn.ConvertNullInt(drc[0]["exec_pos_seq"]);
                    info.exec_pos_name = drc[0]["exec_pos_name"].ToString();
                    info.active = drc[0]["active"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public M_HRM_PS_Employees GetAddressInfo(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_HRM_PS_Employees info = new M_HRM_PS_Employees();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);
                //citizenid address 
                strSQL = "SELECT c.emp_seq, c.citizen as pos_name"
                    + ", concat(coalesce(np.prefix_short, np.prefix_name, ''), coalesce(c.first_name, c.first_name_eng, ''), ' ', coalesce(c.last_name, c.last_name_eng, '')) as emp_name"
                    + ", Concat(ct.contact_address,' ',ct.contact_postal_code) as work_area_name"                   
                    + " FROM hrm_ps_employees c"
                    + " LEFT JOIN hrm_ps_emp_contacts ct ON c.emp_seq = ct.emp_seq"  
                    + " LEFT JOIN mas_name_prefixes np on c.name_prefix_seq = np.name_prefix_seq"
                    + " WHERE c.Record_Status not LIKE @delete_status"
                    + " AND c.emp_seq = @seq";

                

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.work_area_name = drc[0]["work_area_name"].ToString();
                    info.emp_name = drc[0]["emp_name"].ToString();
                    info.emp_seq = Convert.ToInt32(drc[0]["emp_seq"]);
                    info.pos_name = drc[0]["pos_name"].ToString();
                   
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }


        //public Nullable<int> GetOldClassSeq(int seq)
        //{
        //    string strSQL;
        //    DataRowCollection drc = null;
        //    M_HRM_PS_Employees info = new M_HRM_PS_Employees();
        //    try
        //    {
        //        dbManager.Open();
        //        dbManager.CreateParameters(1);

        //        dbManager.AddParameters(0, "@seq", seq);

        //        strSQL = "select e.old_class_seq"
        //            + " from hrm_ps_employees e"
        //            + " left join mas_name_prefixes np on e.name_prefix_seq = np.name_prefix_seq"
        //            + " left join hrm_ps_employee_types et on e.emp_type_seq = et.emp_type_seq"
        //            + " left join hrm_mp_classes c on e.class_seq = c.class_seq"
        //            + " left join hrm_mp_positions p on e.pos_seq = p.pos_seq"
        //            + " left join hrm_mp_job_sites pj on p.job_site_seq = pj.job_site_seq"
        //            + " left join hrm_mp_job_sites wj on e.working_job_site_seq = wj.job_site_seq"
        //            + " left join hrm_ps_employee_statuses es on e.emp_status_seq = es.emp_status_seq"
        //            + " where e.emp_seq = @seq";
        //        

        //        drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
        //        if (drc.Count > 0)
        //        {
        //            info.emp_seq = Convert.ToInt32(drc[0]["emp_seq"]);
        //            info.name_prefix_name = drc[0]["prefix_name"].ToString();
        //            info.first_name = drc[0]["first_name"].ToString();
        //            info.last_name = drc[0]["last_name"].ToString();
        //            info.emp_name = drc[0]["emp_name"].ToString();
        //            info.emp_type_seq = cn.ConvertNullInt(drc[0]["emp_type_seq"]);
        //            info.emp_type_name = drc[0]["emp_type_name"].ToString();
        //            info.class_seq = cn.ConvertNullInt(drc[0]["class_seq"]);
        //            info.class_name = drc[0]["class_name"].ToString();
        //            info.salary = cn.ConvertNullDouble(drc[0]["salary"]);
        //            info.pos_seq = cn.ConvertNullInt(drc[0]["pos_seq"]);
        //            info.pos_ref = drc[0]["pos_ref"].ToString();
        //            info.pos_name = drc[0]["pos_name"].ToString();
        //            info.job_site_ref = drc[0]["job_site_ref"].ToString();
        //            info.job_site_name = drc[0]["job_site_name"].ToString();
        //            info.working_job_site_seq = cn.ConvertNullInt(drc[0]["working_job_site_seq"]);
        //            info.working_job_site_ref = drc[0]["working_job_site_ref"].ToString();
        //            info.working_job_site_name = drc[0]["working_job_site_name"].ToString();
        //            info.emp_status_seq = cn.ConvertNullInt(drc[0]["emp_status_seq"]);
        //            info.emp_status_name = drc[0]["working_job_site_name"].ToString();
        //            info.work_area_seq = cn.ConvertNullInt(drc[0]["work_area_seq"]);
        //            info.active = drc[0]["active"].ToString();
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        throw (ex);
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    return info;
        //}
    }
}
